﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace InspectAll
{
    public class ReportHandler
    {
        Application app = null;
        Workbook workBook = null;



        public void ProcessMyWork()
        {
            try
            {

                using (DataSet ds = ReadDataFromDB())
                {                    
                    CreateWorkbook();

                    WriteLast_PP_InspectionDataToReport(ds);

                    WriteRawDataFromDbToExcel(ds);
                }
                CreateRawPivotTableForPivotChartFromData();

                CreatePivotTableFromData();

                DrawPivotChartFromRawPivotTable();

                (workBook.Worksheets["ReportChart"] as Worksheet).Move(Before: (workBook.Worksheets["RawDataPivotTable"] as Worksheet));
                (workBook.Worksheets["ReportTable"] as Worksheet).Move(Before: (workBook.Worksheets["ReportChart"] as Worksheet));
                (workBook.Worksheets["Sheet1"] as Worksheet).Delete();
                SaveWorkbook();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw new Exception(ex.Message);

            }
            finally
            {
                Dispose();
            }
        }



        public Worksheet GetReportWorkSheet()
        {
            Worksheet reportSheet = null;
            if (!HasSheet("ReportTable"))
            {
                reportSheet = workBook.Worksheets.Add() as Worksheet;
                reportSheet.Name = "ReportTable";
            }
            else
            {
                reportSheet = workBook.Worksheets["ReportTable"] as Worksheet;
            }
            return reportSheet;
        }

        private bool HasSheet(string sheetName)
        {

            if (workBook.Worksheets.Count == 0)
            {
                return false;
            }

            Sheets sheets = workBook.Worksheets;
            foreach (Worksheet item in sheets)
            {
                if (item.Name == sheetName)
                {
                    return true;
                }
            }
            return false;
        }

        private void CreateWorkbook()
        {
            app = new Application();
            workBook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        }


        public void WriteLast_PP_InspectionDataToReport(DataSet ds)
        {
            Worksheet reportSheet = GetReportWorkSheet();
            System.Data.DataTable last_pp_inspectionDT = ds.Tables["last_pp_inspection"];

            reportSheet.Range["A1", "B1"].Merge(true);
            reportSheet.Cells[1, 1] = "Date of Last Inspectation";
            ((Range)reportSheet.Cells[1, 1]).Font.Bold = true;

            for (int i = 0; i < last_pp_inspectionDT.Columns.Count; i++)
            {
                reportSheet.Cells[2, i + 1] = last_pp_inspectionDT.Columns[i].ColumnName;
                (reportSheet.Cells[2, i + 1] as Range).Font.Bold = true;
                (reportSheet.Cells[2, i + 1] as Range).BorderAround2(
                    XlLineStyle.xlContinuous,
                    XlBorderWeight.xlThin,
                    XlColorIndex.xlColorIndexAutomatic,
                    Type.Missing,
                    Type.Missing
                    );
            }

            for (int i = 0; i < last_pp_inspectionDT.Rows.Count; i++)
            {
                for (int j = 0; j < last_pp_inspectionDT.Columns.Count; j++)
                {
                    reportSheet.Cells[i + 3, j + 1] = last_pp_inspectionDT.Rows[i][j];
                    (reportSheet.Cells[i + 3, j + 1] as Range).BorderAround2(
                        XlLineStyle.xlContinuous,
                        XlBorderWeight.xlThin,
                        XlColorIndex.xlColorIndexAutomatic,
                        Type.Missing,
                        Type.Missing
                        );
                }
            }

            reportSheet.Columns.AutoFit();
        }

        private void WriteRawDataFromDbToExcel(DataSet ds)
        {
            Worksheet newSheet = workBook.Worksheets.Add() as Worksheet;
            newSheet.Name = "RawDataFromDb";

            System.Data.DataTable dbDT = ds.Tables["InspectAll"];

            for (int i = 0; i < dbDT.Columns.Count; i++)
            {
                newSheet.Cells[1, i + 1] = dbDT.Columns[i].ColumnName;
            }

            for (int iRow = 0; iRow < dbDT.Rows.Count; iRow++)
            {
                for (int jColumn = 0; jColumn < dbDT.Columns.Count; jColumn++)
                {
                    newSheet.Cells[iRow + 2, jColumn + 1] = dbDT.Rows[iRow][jColumn];
                }
            }
            newSheet.Columns.AutoFit();
        }

        private void CreateRawPivotTableForPivotChartFromData()
        {
            Worksheet newSheet = workBook.Worksheets.Add() as Worksheet;
            newSheet.Name = "RawDataPivotTable";

            Range pivotData = GetPivotData();

            PivotTable pt = newSheet.PivotTableWizard(
                 XlPivotTableSourceType.xlDatabase,
                 pivotData,
                 newSheet.Range["A1", Type.Missing],
                 "PivotTable2",
                 false, false, true, false, Type.Missing, Type.Missing,
                 false, false, XlOrder.xlDownThenOver, Type.Missing,
                 Type.Missing, Type.Missing);

            PivotField runDateField = pt.PivotFields("RunDate") as PivotField;
            PivotField failureTypeField = pt.PivotFields("Failure_Type") as PivotField;

            runDateField.Orientation = XlPivotFieldOrientation.xlRowField;
            runDateField.Position = 1;

            failureTypeField.Orientation = XlPivotFieldOrientation.xlColumnField;
            failureTypeField.Position = 1;

            pt.AddDataField(pt.PivotFields("Count"),
                "Sum of Count", XlConsolidationFunction.xlSum);

            newSheet.Columns.AutoFit();
        }

        private void CreatePivotTableFromData()
        {
            //PivotTable Sheet
            Range pivotData = GetPivotData();

            Worksheet newSheet = GetReportWorkSheet();

            var lastRowIndex = GetLastRowIndex(newSheet);
            Range belowLastRowRange = newSheet.Cells[lastRowIndex + 2, 1] as Range;

            PivotTable pt = newSheet.PivotTableWizard(
                 XlPivotTableSourceType.xlDatabase,
                 pivotData,
                 belowLastRowRange,
                 "PivotTable1",
                 false, false, true, false, Type.Missing, Type.Missing,
                 false, false, XlOrder.xlDownThenOver, Type.Missing,
                 Type.Missing, Type.Missing);

            PivotField runDateField = pt.PivotFields("RunDate") as PivotField;
            PivotField failureTypeField = pt.PivotFields("Failure_Type") as PivotField;

            runDateField.Orientation = XlPivotFieldOrientation.xlColumnField;
            runDateField.Position = 1;

            failureTypeField.Orientation = XlPivotFieldOrientation.xlRowField;
            failureTypeField.Position = 1;

            pt.AddDataField(pt.PivotFields("Count"),
                "Sum of Count", XlConsolidationFunction.xlSum);

            newSheet.Columns.AutoFit();
        }

        private void DrawPivotChartFromRawPivotTable()
        {
            Worksheet rawPivotTableSheet = workBook.Worksheets["RawDataPivotTable"] as Worksheet;
            Range pivotData = rawPivotTableSheet.Range["A1", Type.Missing];

            Worksheet newSheet = workBook.Worksheets.Add() as Worksheet;
            newSheet.Name = "ReportChart";

            var lastRowIndex = GetLastRowIndex(newSheet);
            Range belowLastRowRange = newSheet.Cells[lastRowIndex + 2, 1] as Range;

            ChartObjects chartObjects = (ChartObjects)newSheet.ChartObjects();

            ChartObject pivotChart = chartObjects.Add((double)belowLastRowRange.Left, (double)belowLastRowRange.Top, 600, 400);


            //Format the PivotChart.
            pivotChart.Chart.ChartWizard(
              pivotData,
              XlChartType.xlColumnStacked,
              1,
              XlRowCol.xlColumns,
              1,
              1,
              true,
              "Periodic Processing Failures (All Inspect)",
              "Run Date",
              "Count",
              "ExtraTitle"
              );

            newSheet.Columns.AutoFit();
        }

        private static int GetLastRowIndex(Worksheet newSheet)
        {
            var lastRowIndex = newSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, 23).Row;
            return lastRowIndex;
        }

        public void SaveWorkbook()
        {
            string filePath = @"C:\testme.xlsx";
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlNoChange);
        }

        private Range GetPivotData()
        {
            Worksheet DbRawDataSheet = workBook.Worksheets["RawDataFromDb"] as Worksheet;
            Range pivotData = DbRawDataSheet.Cells.SpecialCells(XlCellType.xlCellTypeConstants, 23);

            return pivotData;
        }

        static void SetRow(Worksheet sheet, int row, params string[] values)
        {
            for (int x = 0; x < values.Length; x++)
            {
                sheet.Cells[row, x + 1] = values[x];
            }
        }

        public DataSet ReadDataFromDB()
        {
            string connStr = "Data Source=.;Integrated Security=SSPI;Initial Catalog =Test";
            DataSet ds = new DataSet();

            SqlConnection conn = null;
            SqlDataAdapter adapter = null;
            SqlDataAdapter adapter2 = null;
            try
            {
                conn = new SqlConnection(connStr);
                conn.Open();
                adapter = new SqlDataAdapter("select * from InspectAll(nolock)", conn);
                adapter2 = new SqlDataAdapter("select * from last_pp_inspection(nolock)", conn);
                adapter.Fill(ds, "InspectAll");
                adapter2.Fill(ds, "last_pp_inspection");
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (adapter2 != null)
                    adapter2.Dispose();
                if (conn != null)
                    conn.Dispose();
            }
            return ds;
        }

        public void Dispose()
        {
            if (workBook != null)
            {
                workBook = null;
            }
            if (app != null)
            {
                app.Quit();
                app = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
        ~ReportHandler()
        {

        }
    }
}
